---
title: "cp_claimprofile_get_sc_book stored procedure documentation"
summary: "Retrieved SQL Server procedure that returns the loss-control consultants active book of business."
status: draft
owner: "SAIF Claims BOF team"
last_reviewed: "2026-08-25"
source_schema: "dbo"
source_name: "cp_claimprofile_get_sc_book"
source_type: "PROCEDURE"
retrieval_status: "found"
retrieval_command: "legends sqlserver query --name cp_claimprofile_get_sc_book"
retrieval_date: "2026-08-25"
---
# cp_claimprofile_get_sc_book

## Overview

| Field | Value |
| --- | --- |
| **Schema** | `dbo` |
| **Type** | Standalone procedure |
| **Database** | SQL Server (`eisD0P11`) |
| **Calling libraries** | `SAIF.Business.View.Policy` |
| **Calling classes** | `clsPolicySecurity` |

## Purpose

`cp_claimprofile_get_sc_book` returns the active policies assigned to a loss-control consultant. The retrieved body matches the consultant by mail ID or wildcard pattern, excludes flat-cancel and inactive policies, and sorts the result by policy number and policy name.

## Procedures / Methods

### cp_claimprofile_get_sc_book

| Field | Value |
| --- | --- |
| **Returns** | Result set of consultant-book policy rows |
| **Called by** | `clsPolicySecurity.GetPolicyListForSafetyConsultant` |
| **Description** | Returns the active policy list for a loss-control consultant. |

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `@losscontrol` | IN | `varchar(8)` | — | Loss-control mail ID or wildcard pattern. |

## Business Rules

The retrieved database source embeds the following server-side rules.

| Rule ID | Summary |
| --- | --- |
| [BR-VP-039](#br-vp-039-safety-consultant-book-retrieval-supports-exact-and-wildcard-loss-control-mail-ids) | The procedure accepts either an exact mail ID or a wildcard pattern depending on whether the input contains `%`. |
| [BR-VP-040](#br-vp-040-safety-consultant-book-excludes-flat-cancel-and-inactive-policies) | Only active policies whose most recent period is not flat-cancel and whose loss-control mail ID is populated are returned. |

### BR-VP-039 Safety-consultant book retrieval supports exact and wildcard loss-control mail IDs

| Field | Value |
| --- | --- |
| **Statement** | The safety-consultant book procedure matches `polTeam.LSS_MAIL_ID` either exactly or with `LIKE`, depending on whether the supplied `@losscontrol` value contains `%`. |
| **Trigger** | `cp_claimprofile_get_sc_book` executes. |
| **Enforcement** | The `WHERE` clause checks `charindex('%', @losscontrol)` and applies either equality or `LIKE rtrim(@losscontrol)`. |
| **Outcome** | Callers can request a single consultant or a wildcard consultant pattern. |
| **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_claimprofile_get_sc_book.json` |

---

### BR-VP-040 Safety-consultant book excludes flat-cancel and inactive policies

| Field | Value |
| --- | --- |
| **Statement** | The safety-consultant book returns only active policies whose most recent policy period is not `FC` and whose loss-control mail ID is present. |
| **Trigger** | `cp_claimprofile_get_sc_book` executes. |
| **Enforcement** | The `WHERE` clause requires `T1.PLCY_PD_STAT_CD <> 'FC'`, `wPol.ACTV_PLCY_FL = 'Y'`, and non-null and non-empty `polTeam.Lss_mail_id`. |
| **Outcome** | Flat-cancel, inactive, and unassigned policies are excluded from the consultants book. |
| **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_claimprofile_get_sc_book.json` |

## Operations

The retrieved database source implements the following internal operations.

| Operation | Description | Trigger |
| --- | --- | --- |
| [Match loss-control consultant identity](#match-loss-control-consultant-identity) | Interpret the supplied loss-control argument as either an exact mail ID or a wildcard pattern. | Caller invokes `cp_claimprofile_get_sc_book`. |
| [Return the active safety-consultant book](#return-the-active-safety-consultant-book) | Return active, non-flat-cancel policies assigned to the matched loss-control consultant. | The filtered query over policy, period, team, and office tables executes. |

### Match loss-control consultant identity

| Field | Value |
| --- | --- |
| **Description** | Interpret the supplied loss-control argument as either an exact mail ID or a wildcard pattern. |
| **Trigger** | Caller invokes `cp_claimprofile_get_sc_book`. |
| **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_claimprofile_get_sc_book.json` |

### Return the active safety-consultant book

| Field | Value |
| --- | --- |
| **Description** | Return active, non-flat-cancel policies assigned to the matched loss-control consultant. |
| **Trigger** | The filtered query over policy, period, team, and office tables executes. |
| **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_claimprofile_get_sc_book.json` |

## Calling code references

| Class | Method | Source File |
| --- | --- | --- |
| `clsPolicySecurity` | `GetPolicyListForSafetyConsultant` | `SAIF.Business.View.Policy\clsPolicySecurity.vb` |

## Source Code

<details>
<summary>SQL Server procedure source</summary>

```sql

Create  procedure dbo.cp_claimprofile_get_sc_book
		@losscontrol varchar(8)
        --@region varchar(30),
        --@renewdate datetime
as
/*--------------------------------------------------------------------------------
Procedure Name:		cp_claimprofile_get_sc_book
Date Written:		02/25/2010
Author:			Jerry Todd (SAIF)
Installation:		SAIF Corporation
Source File Name:	cp_claimprofile_get_sc_book
Description:		This procedure gets Accounts
					It is based on cp_lc_active_book
Modification Log:   	
--------------------------------------------------------------------------------*/
set nocount on
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;


Declare
	@bds_cutoff_dt datetime
	set @bds_cutoff_dt= eis_prod.dbo.fn_policy_cutoff_dt()

 select
  wPol.plcy_nam POLICY_NAME, 
------
  wPol.PLCY_NUM POLICY_NUMBER
------

from eis_prod.dbo.WCS_POLICY wPol
--	join eis_prod.dbo.
join eis_prod.dbo.WCS_POLICY_PERIOD T1
	on wPol.plcy_num=T1.plcy_num and T1.MOST_RECENT_PP_COV_FL='Y'
join eis_prod.dbo.WCS_POLICY_PERIOD_SUM T2
	on T1.PLCY_PD_ID=T2.PLCY_PD_ID
join eis_prod.dbo.wcs_policy_sum wSum 
	on wPol.plcy_id=wSum.plcy_id
-------------------------------
--get agency information
	--join EIS_PROD.dbo.WCS_AGENT wa 
	--	on wa.AGT_ID=wSum.AGT_ID
--------------------------------
JOIN eis_prod.dbo.wcs_office wo
	on wo.OFC_ID = wSum.ofc_id	
join eis_prod.dbo.wcs_policy_team polTeam 
	on polTeam.plcy_team_id=wSum.plcy_team_id

left outer join eis_prod.dbo.wcs_team_mbr polMembrDW
    on polTeam.MKTG_REP_MAIL_ID = polMembrDW.USER_MAIL_ID

join eis_prod.dbo.wcs_team_mbr polMembrLC
        on polTeam.LSS_MAIL_ID = polMembrLC.USER_MAIL_ID
left join eisd0p11.dbo.dbs_county cty on wPol.county_cd = cty.county_cd

Where ((polTeam.Lss_mail_id = @losscontrol
  and charindex('%', @losscontrol) = 0)
  or 
   (polTeam.Lss_mail_id like rtrim(@losscontrol)
 and charindex('%', @losscontrol) > 0)) 
--excludes Flat Cancel
AND T1.PLCY_PD_STAT_CD<>'FC'
AND wPol.ACTV_PLCY_FL='Y'



AND ((polTeam.Lss_mail_id is not null) and (polTeam.Lss_mail_id != ''))


order by  POLICY_NUMBER,POLICY_NAME


Return
```

</details>

## Related documentation
- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)

## Sources
- SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_claimprofile_get_sc_book.json`
- Calling code: `SAIF.Business.View.Policy\clsPolicySecurity.vb`